package com.theydream.qeechain.db;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import com.theydream.qeechain.globaldata.CaughtItemListClass;
import com.theydream.qeechain.globaldata.GlobalData;
import com.theydream.qeechain.globaldata.ItemListClass;
import com.theydream.qeechain.globaldata.definition.CaughtPet;
import com.theydream.qeechain.globaldata.definition.Effect;
import com.theydream.qeechain.globaldata.definition.LocationData;
import com.theydream.qeechain.globaldata.definition.Qee;
import com.theydream.qeechain.globaldata.definition.Unlocker;
import com.theydream.qeechain.publicmethod.PublicMethod;

public class DBHelper extends SQLiteOpenHelper{
	 
    //The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/com.theydream.qeechain/databases/";
 
    private static String DB_NAME = "iQ.db";
    
    private SQLiteDatabase myDataBase; 
 
    private final Context myContext;
 
    /**
     * Constructor
     * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
     * @param context
     */
    public DBHelper(Context context) {
 
    	super(context, DB_NAME, null, 1);
        this.myContext = context;
    }	
 
  /**
     * Creates a empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() throws IOException{
 
    	boolean dbExist = checkDataBase();
 
    	if(dbExist){
    		//do nothing - database already exist
    	}else{
 
    		//By calling this method and empty database will be created into the default system path
               //of your application so we are gonna be able to overwrite that database with our database.
        	this.getReadableDatabase();
 
        	try {
 
    			copyDataBase();
 /*       		Date time = new Date();
        		DateFormat format = new SimpleDateFormat("mm:HH dd-MM-yyyy");
        		myDataBase.execSQL("INSERT INTO user_has_pet(pid, lat, lng, time, attr, plevel, in_use) VALUES " +
        				"(?, ?, ?, ?, ?, ?)", new String [] {"3", "22.285492799414733", "114.15916085243225", format.format(time), "9;4;7;2;3;4;1", "3", "1"});
*/ 
    		} catch (IOException e) {
 
        		throw new Error("Error copying database");
 
        	}
    	}
 
    }
 
    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
     */
    private boolean checkDataBase(){
 
    	SQLiteDatabase checkDB = null;
 
    	try{
    		String myPath = DB_PATH + DB_NAME;
    		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
 
    	}catch(SQLiteException e){
 
    		//database does't exist yet.
 
    	}
 
    	if(checkDB != null){
 
    		checkDB.close();
 
    	}
 
    	return checkDB != null ? true : false;
    }
 
    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     * */
    private void copyDataBase() throws IOException{
 
    	//Open your local db as the input stream
    	InputStream myInput = myContext.getAssets().open(DB_NAME);
 
    	// Path to the just created empty db
    	String outFileName = DB_PATH + DB_NAME;
 
    	//Open the empty db as the output stream
    	OutputStream myOutput = new FileOutputStream(outFileName);
 
    	//transfer bytes from the inputfile to the outputfile
    	byte[] buffer = new byte[1024];
    	int length;
    	while ((length = myInput.read(buffer))>0){
    		myOutput.write(buffer, 0, length);
    	}
 
    	//Close the streams
    	myOutput.flush();
    	myOutput.close();
    	myInput.close();
 
    }
 
    public void openDataBase() throws SQLException{
 
    	//Open the database
        String myPath = DB_PATH + DB_NAME;
    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
 
    }
 
    @Override
	public synchronized void close() {
 
    	    if(myDataBase != null)
    		    myDataBase.close();
 
    	    super.close();
 
	}
 
	@Override
	public void onCreate(SQLiteDatabase db) {
 
	}
 
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
	}
 
        // Add your public helper methods to access and get content from the database.
       // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
       // to you to create adapters for your views.
	
	public void loadPetInfo()	{
//		GlobalData.getInstance().getPetList().clear();
		Map<String, Qee> map = new HashMap<String, Qee>();
		Cursor cursor = null;
		try	{
			cursor = myDataBase.rawQuery("SELECT _id, attr, name, desc, pid FROM pet", null);
			if (cursor.moveToFirst())	{
				do	{
					String id = cursor.getString(cursor.getColumnIndexOrThrow("_id"));
					String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
					String desc = cursor.getString(cursor.getColumnIndexOrThrow("desc"));
					String pid = cursor.getString(cursor.getColumnIndexOrThrow("pid"));
					Map<String, Integer> attr = PublicMethod.parseAttr(cursor.getString(cursor.getColumnIndexOrThrow("attr")));
					Map<String, Integer> stat = PublicMethod.generateStat(attr);
					GlobalData.getInstance().getPetList();
					map.put(id, new Qee(name, desc, attr, stat, pid));
				} while (cursor.moveToNext());
				GlobalData.getInstance().setPetList(map);
			}
		}
		finally	{
			cursor.close();
		}
	}
	
	public void loadMapItem()	{	
		ItemListClass.getInstance().getItemList().clear();
		Cursor cursor = null;
		try	{
			cursor = myDataBase.rawQuery("SELECT type, lat, lng, name, pid, prob, item_id FROM map_item, pet WHERE map_item.item_id = pet._id", null);
			if (cursor.moveToFirst())	{
				do	{
			        LocationData itemStruct=new LocationData();
			        itemStruct.setTitle(cursor.getString(cursor.getColumnIndexOrThrow("name")));
			        itemStruct.setLat(cursor.getDouble(cursor.getColumnIndexOrThrow("lat")));
			        itemStruct.setLng(cursor.getDouble(cursor.getColumnIndexOrThrow("lng")));
			        itemStruct.setPic(cursor.getString(cursor.getColumnIndexOrThrow("pid")));
			        itemStruct.setType(Integer.parseInt(cursor.getString(cursor.getColumnIndexOrThrow("type"))));
			        itemStruct.setProbability(cursor.getDouble(cursor.getColumnIndexOrThrow("prob")));
			        itemStruct.setId(cursor.getString(cursor.getColumnIndexOrThrow("item_id")));
			        ItemListClass.addToItemList(itemStruct);
				} while (cursor.moveToNext());
			}
		}
		finally	{
			cursor.close();
		}
	}
	
	public void insertCaughtPet(String pid, String lat, String lng, String attr, String level, String caught_pic)	{
		
		Date time = new Date();
		DateFormat format = new SimpleDateFormat("mm:HH dd-MM-yyyy");
		myDataBase.execSQL("INSERT INTO user_has_pet(pid, lat, lng, time, attr, plevel) VALUES " +
				"(?, ?, ?, ?, ?, ?)", new String [] {pid, lat,lng, format.format(time), attr, level});
		
		// insert into CaughtPet list
		CaughtItemListClass.addToItemList(new CaughtPet(GlobalData.getInstance().getPetList().get(pid), lat, lng, time.toString(), caught_pic));
	}
	
	public void loadCaughtPets()	{
		CaughtItemListClass.getInstance().getItemList().clear();
		GlobalData.getInstance().getPetInUse().clear();
		Cursor cursor = null;
		
		try	{
		cursor = myDataBase.rawQuery("SELECT pid, lat, lng, time, attr, plevel, caught_pic, in_use FROM user_has_pet", null);
			if (cursor.moveToFirst())	{
				do	{
					Qee qee = GlobalData.getInstance().getPetList().get(cursor.getString(cursor.getColumnIndexOrThrow("pid")));
					Map<String, Integer> attrMap = PublicMethod.parseAttr(cursor.getString(cursor.getColumnIndexOrThrow("attr")));
					Map<String, Integer> statMap = PublicMethod.generateStat(attrMap);
					qee.setAttr(attrMap);
					qee.setStat(statMap);
					
					String lat = cursor.getString(cursor.getColumnIndexOrThrow("lat"));
					String lng = cursor.getString(cursor.getColumnIndexOrThrow("lng"));
					String time = cursor.getString(cursor.getColumnIndexOrThrow("time"));
					String caught_pic = cursor.getString(cursor.getColumnIndexOrThrow("caught_pic"));
					
					CaughtItemListClass.addToItemList(new CaughtPet(qee, lat, lng, time, caught_pic));
					
					if ("1".equals(cursor.getString(cursor.getColumnIndexOrThrow("in_use"))))	{
						GlobalData.getInstance().getPetInUse().add(qee);
//						Log.i("LoadingPet", Integer.toString(GlobalData.getInstance().getPetInUse().size()));
					}
					
				} while (cursor.moveToNext());
			}
		}
		finally	{
			cursor.close();
		}
	}
	
	public void loadSkill()	{
//		GlobalData.getInstance().getSkills().clear();
		Map<String, Effect> map = new HashMap<String, Effect>();
		Cursor cursor = null;
		
		try	{
			cursor = myDataBase.rawQuery("SELECT visual, effect, sequence FROM skill", null);
			if (cursor.moveToFirst())	{
				do	{
					String key = cursor.getString(cursor.getColumnIndexOrThrow("sequence"));
					String effect = cursor.getString(cursor.getColumnIndexOrThrow("effect"));
					Integer delay = Integer.valueOf(effect.split(";")[3]);
					String visual = cursor.getString(cursor.getColumnIndexOrThrow("visual"));
					Effect val = new Effect(effect, delay, visual);
					map.put(key, val);
				}	while(cursor.moveToNext());
			}
			GlobalData.getInstance().setSkills(map);
		}
		finally	{
			cursor.close();
		}
	}
	
	// Get the list of skill id that the user can use
	public Set<String> getUserSkill()	{
		Set<String> userSkill = new HashSet<String>();
		Cursor cursor = null;
		try	{
			cursor = myDataBase.rawQuery("SELECT sequence FROM skill WHERE user_has = '1'", null);
			if (cursor.moveToFirst())	{
				do	{
					userSkill.add(cursor.getString(cursor.getColumnIndexOrThrow("sequence")));
				} while(cursor.moveToNext());
			}	
			return userSkill;
		}
		finally	{
			cursor.close();
		}
	}
	
	public void setPetInUse(Integer record_id)	{
		try	{
			myDataBase.execSQL("UPDATE user_has_pet SET in_use = 0 WHERE in_use = 1");
			myDataBase.execSQL("UPDATE user_has_pet SET in_use = 1 WHERE _id = ?", new String [] {Integer.toString((record_id+1))});
			GlobalData.getInstance().getPetInUse().clear();
			
			Qee qee = CaughtItemListClass.getInstance().getItemList().get(record_id).getPet();
			GlobalData.getInstance().getPetInUse().add(qee);
		}
		finally	{
		}
	}
	
	public int getPetInUse()	{
		Cursor cursor = null;
		try	{
			cursor = myDataBase.rawQuery("SELECT _id FROM user_has_pet WHERE in_use = 1", null);
			cursor.moveToFirst();
			return Integer.valueOf(cursor.getString(cursor.getColumnIndexOrThrow("_id"))) - 1; 
		}
		finally	{
			cursor.close();
		}
	}
	
	public List<Unlocker> getUnlocker()	{
		List<Unlocker> listUnlocker = new ArrayList<Unlocker>();
		Cursor cursor = null;
		try	{
			cursor = myDataBase.rawQuery("SELECT name, desc, pic FROM boss_unlocker WHERE collected = 1", null);
			if (cursor.moveToFirst())	{
				do {
					String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
					String desc = cursor.getString(cursor.getColumnIndexOrThrow("desc"));
					String pic = cursor.getString(cursor.getColumnIndexOrThrow("pic"));
					listUnlocker.add(new Unlocker(name, desc, pic));
				} while (cursor.moveToNext());
			}
			return listUnlocker;
		}
		finally	{
			cursor.close();
		}
	}
	
	public Unlocker getUnlockerIfNotCollected(String id)	{
		Cursor cursor = null;
		try	{
			cursor = myDataBase.rawQuery("SELECT name, desc, pic FROM boss_unlocker WHERE _id = ? AND collected = 0", new String[] {id});
			if (cursor.moveToFirst())	{
				String name = cursor.getString(cursor.getColumnIndexOrThrow("name"));
				String desc = cursor.getString(cursor.getColumnIndexOrThrow("desc"));
				String pic = cursor.getString(cursor.getColumnIndexOrThrow("pic"));
				return new Unlocker(name, desc, pic);
			}
			return null;
		}
		finally	{
			cursor.close();
		}
	}
	
	public void setUnlocker(String id, String status)	{
		try	{
			myDataBase.execSQL("UPDATE boss_unlocker SET collected = ? WHERE _id = ?", new String [] {status, id});
		}
		finally	{
			
		}
	}
	
	public Integer getUnlockerTotal()	{
		Cursor cursor = null;
		try	{
			cursor = myDataBase.rawQuery("SELECT COUNT(*) AS count FROM boss_unlocker", null);
			cursor.moveToFirst();
			return Integer.valueOf(cursor.getString(cursor.getColumnIndexOrThrow("count")));
		}
		finally	{
			cursor.close();
		}
		
	}
}
